﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Worktool
{
    public partial class Baufortschritt : Form
    {
               
        
        public Baufortschritt()
        {
            InitializeComponent();
        }

        private void Baufortschritt_Load(object sender, EventArgs e)
        {
            this.Text = "Baufortschritt: " + Properties.Settings.Default.CurrentDBName; 
            this.tbl_CableListTableAdapter.Fill(this.aSCDataSet.tbl_CableList);
            this.vw_bauStatusTableAdapter.Fill(this.aSCDataSet.vw_bauStatus);
            
            this.sTAMMDATEN_areasTableAdapter.Fill(this.aSCDataSet.STAMMDATEN_areas);
            //this.vwbauStatusBindingSource.Filter = string.Format("Venue LIKE '{0}' AND Positions_ID <> ''", (comboBox1.Text + "*"));

            // Eventhandler erstellen
            this.dataGridView1.CellValueChanged += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView1_CellValueChanged);
            this.dataGridView2.CellValueChanged += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView2_CellValueChanged);

        }

        
        //
        // Filter setzen bei erreichen einer neuen Reihe im Baufortschritt
        //
        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1["Positions_ID", e.RowIndex].Value.ToString() != "")
            {
                try
                {
                    this.tblCableListBindingSource.Filter = string.Format("CableFrom2 LIKE '{0}' OR CableTo2 LIKE '{0}'", dataGridView1["Positions_ID", e.RowIndex].Value.ToString());
                    dataGridView2.Update();

                }
                catch (Exception ex)
                { }
            }
            else
            {
                this.tblCableListBindingSource.Filter = string.Format("CableFrom2 LIKE 'ABCDEFGHIJKLMN'");
            }
        }

        //
        // Alle Kabel aus dem Bereich anzeigen
        //
        private void button1_Click(object sender, EventArgs e)
        {
            string venueIdent = "ABCDE";
            List<string> posList = new List<string> { };
            string retValue = "";
            string venue = "";
            int reihen = 0;
                
            if (textBox1.Text != "")
            {
                
                if (dataGridView1.RowCount > 0)
                {
                    //Die richtige Venue ID aus der ersten Zelle auslesen                
                    venue = dataGridView1.Rows[0].Cells["Venue"].Value.ToString();
                    foreach(DataRow dr in aSCDataSet.STAMMDATEN_areas.Rows)
                    {
                        if (dr["Venue"].ToString() == venue)
                        {
                            venueIdent = dr["Venue ID MW"].ToString();
                        }
                        
                    }

                    //venueID = aSCDataSet.STAMMDATEN_areas.Select("Venue LIKE '%" + venue + "%'")[0]["Venue ID MW"].ToString();
                    int a = 0;
                }

                
                for (int a = 0; a < dataGridView1.RowCount; a++)
                {

                    if (dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString() != "")
                    {
                        posList.Add(dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString());
                    }

                }
                try
                {
                    retValue = "'" + String.Join("','", posList) + "'";
                    this.tblCableListBindingSource.Filter = string.Format("(CableFrom2 IN ("+retValue +") OR (ISNULL(CableFrom2,'') = '' AND Cableto2 IN ("+retValue+"))) OR (Cableto2 IN ("+retValue+") OR (ISNULL(Cableto2,'') = '' AND CableFrom2 IN ("+retValue+")))");

                }
                catch (Exception ex)
                { } 
            }
        }

        //
        // Nur die Non-Rack-Label aus dem Bereich anzeigen
        //
        private void button2_Click(object sender, EventArgs e)
        {
            List<string> posList = new List<string> { };
            string retValue = "";
            if (textBox1.Text != "")
            {
                for (int a = 0; a < dataGridView1.RowCount; a++)
                {

                    try
                    {

                        if (dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString().Length > 4)
                        {
                            if (dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString().Substring(0, 4) != "ENSR" &&
                                                dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString().Substring(0, 4) != "ENLR" &&
                                                dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString().Substring(0, 4) != "ENVR" &&
                                                dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString().Substring(0, 4) != "ELRR")
                            {
                                posList.Add(dataGridView1.Rows[a].Cells["Positions_ID"].Value.ToString());
                            } 
                        }
                    }
                    catch (Exception ex)
                    { }

                }
                try
                {
                    retValue = "'" + String.Join("','", posList)  + "'";
                    this.tblCableListBindingSource.Filter = string.Format("((CableFrom2 IN (" + retValue + ") OR ISNULL(CableFrom2,'') = '') AND Cableto2 IN (" + retValue + ")) OR ((Cableto2 IN (" + retValue + ") OR ISNULL(Cableto2,'') = '') AND CableFrom2 IN (" + retValue + "))");
                    
                }
                catch (Exception ex)
                { } 
            }
        }

        private void pasteYESToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.SelectedCells.Count; i++)
            {
                if (!dataGridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateDate"].Value = DateTime.Today;
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateUser"].Value = Properties.Settings.Default.User;
                        dataGridView1.SelectedCells[i].Value = "YES";
                    }
                    catch (Exception ex)
                    { }
                }
            }
        }

        private void pasteNOToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.SelectedCells.Count; i++)
            {
                if (!dataGridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateDate"].Value = DateTime.Today;
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateUser"].Value = Properties.Settings.Default.User;
                        dataGridView1.SelectedCells[i].Value = "NO";
                    }
                    catch (Exception ex)
                    { }
                }
            }
        }

        private void pasteNAToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.SelectedCells.Count; i++)
            {
                if (!dataGridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateDate"].Value = DateTime.Today;
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateUser"].Value = Properties.Settings.Default.User;
                        dataGridView1.SelectedCells[i].Value = "N/A";
                    }
                    catch (Exception ex)
                    { }
                }
            }
        }

        private void clearToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.SelectedCells.Count; i++)
            {
                if (!dataGridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateDate"].Value = DateTime.Today;
                        dataGridView1.SelectedCells[i].OwningRow.Cells["updateUser"].Value = Properties.Settings.Default.User;
                        dataGridView1.SelectedCells[i].Value = "";
                    }
                    catch (Exception ex)
                    { }
                }
            }
        }

        private void setToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView2.SelectedCells.Count; i++)
            {
                
                    try
                    {
                        dataGridView2.SelectedCells[i].Value = true;
                    }
                    catch (Exception ex)
                    { }
             
            }
        }

        private void resetToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView2.SelectedCells.Count; i++)
            {
                    try
                    {

                        dataGridView2.SelectedCells[i].Value = false;
                    }
                    catch (Exception ex)
                    { }
            }
        }




        //
        // Änderungen in die Fertigstellungsprozente übertragen
        //
        private void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            
            //float erledigtStart = 0;
            //float erledigtEnd = 0;
            //float gesamtStart = 0;
            //float gesamtEnd = 0;
            //float ergebnis = 0;
            //string startPos = "";
            //string endPos = "";

            DataRow dr;
            DataRowView drv;

            tblCableListBindingSource.EndEdit();
            // Nur die einzelnen Zeilen aktualisieren
            drv = (DataRowView)dataGridView2.CurrentRow.DataBoundItem;
            dr = drv.Row;
            tbl_CableListTableAdapter.Update(aSCDataSet.tbl_CableList);
            #region prozente
            /*
            if (e.ColumnIndex == dataGridView2.Columns["isInstalledStart"].Index || e.ColumnIndex == dataGridView2.Columns["isInstalledEnd"].Index)
            {
                startPos = dataGridView2.Rows[e.RowIndex].Cells["CableFrom2"].Value.ToString();
                endPos = dataGridView2.Rows[e.RowIndex].Cells["CableTo2"].Value.ToString();

                foreach (DataRow row in aSCDataSet.tbl_CableList.Rows)
                {
                    if (row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos)
                    {
                        gesamtStart++;
                    }
                    if (row["CableFrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos)
                    {
                        gesamtEnd++;
                    }
                    if (!Convert.IsDBNull(row["isInstalledStart"]))
                    {
                        if ((row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos) && (Convert.ToBoolean(row["isInstalledStart"]) == true))
                        {
                            erledigtStart++;
                        }
                    }
                    if (!Convert.IsDBNull(row["isInstalledEnd"]))
                    {
                        if ((row["Cablefrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos) && (Convert.ToBoolean(row["isInstalledEnd"]) == true))
                        {
                            erledigtEnd++;
                        }
                    }
                }

                foreach (DataRow row in aSCDataSet.vw_bauStatus)
                {
                    if (row["Positions_ID"].ToString() == startPos)
                    {
                        ergebnis = erledigtStart / gesamtStart * 100;
                        row["cablesInstalled"] = ergebnis.ToString();
                        row["updateDate"] = DateTime.Today;
                        row["updateUser"] = Properties.Settings.Default.User;
                    }
                    if (row["Positions_ID"].ToString() == endPos)
                    {
                        ergebnis = erledigtEnd / gesamtEnd * 100;
                        row["cablesInstalled"] = ergebnis.ToString();
                        row["updateDate"] = DateTime.Today;
                        row["updateUser"] = Properties.Settings.Default.User;
                    }
                }
                // Zurückschreiben in die Datenbank
                vwbauStatusBindingSource.EndEdit();         
                vw_bauStatusTableAdapter.Update(aSCDataSet.vw_bauStatus);
                
                // Datagrid aktualiseren
                dataGridView1.Update();
            }

                //----------- Terminieren


                if (e.ColumnIndex == dataGridView2.Columns["isTerminatedStart"].Index || e.ColumnIndex == dataGridView2.Columns["isTerminatedEnd"].Index)
            {
                startPos = dataGridView2.Rows[e.RowIndex].Cells["CableFrom2"].Value.ToString();
                endPos = dataGridView2.Rows[e.RowIndex].Cells["CableTo2"].Value.ToString();

                foreach (DataRow row in aSCDataSet.tbl_CableList.Rows)
                {
                    if (row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos)
                    {
                        gesamtStart++;
                    }
                    if (row["CableFrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos)
                    {
                        gesamtEnd++;
                    }
                    if(!Convert.IsDBNull(row["isTerminatedStart"]))
                    {
                        if ((row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos) && (Convert.ToBoolean(row["isTerminatedStart"]) == true))
                        {
                            erledigtStart++;
                        }
                    }
                    if (!Convert.IsDBNull(row["isTerminatedEnd"]))
                    {
                        if ((row["Cablefrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos) && (Convert.ToBoolean(row["isTerminatedEnd"]) == true))
                        {
                            erledigtEnd++;
                        }
                    }
                }

                foreach (DataRow row in aSCDataSet.vw_bauStatus)
                {
                    if (row["Positions_ID"].ToString() == startPos)
                    {
                        ergebnis = erledigtStart / gesamtStart * 100;
                        row["cablesTerminated"] = ergebnis.ToString();
                        row["updateDate"] = DateTime.Today;
                        row["updateUser"] = Properties.Settings.Default.User;
                    }
                    if (row["Positions_ID"].ToString() == endPos)
                    {
                        ergebnis = erledigtEnd / gesamtEnd * 100;
                        row["cablesTerminated"] = ergebnis.ToString();
                        row["updateDate"] = DateTime.Today;
                        row["updateUser"] = Properties.Settings.Default.User;
                    }
                }
                // Zurückschreiben in die Datenbank
                vwbauStatusBindingSource.EndEdit();
                vw_bauStatusTableAdapter.Update(aSCDataSet.vw_bauStatus);
                // Datagrid aktualiseren
                dataGridView1.Update();
                } 

                     //----------- Testen


                if (e.ColumnIndex == dataGridView2.Columns["isTestedStart"].Index || e.ColumnIndex == dataGridView2.Columns["isTestedEnd"].Index)
                {
                    startPos = dataGridView2.Rows[e.RowIndex].Cells["CableFrom2"].Value.ToString();
                    endPos = dataGridView2.Rows[e.RowIndex].Cells["CableTo2"].Value.ToString();

                    foreach (DataRow row in aSCDataSet.tbl_CableList.Rows)
                    {
                        if (row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos)
                        {
                            gesamtStart++;
                        }
                        if (row["CableFrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos)
                        {
                            gesamtEnd++;
                        }
                        if (!Convert.IsDBNull(row["isTestedStart"]))
                        {
                            if ((row["Cablefrom2"].ToString() == startPos || row["Cableto2"].ToString() == startPos) && (Convert.ToBoolean(row["isTestedStart"]) == true))
                            {
                                erledigtStart++;
                            }
                        }
                        if (!Convert.IsDBNull(row["isTestedEnd"]))
                        {
                            if ((row["Cablefrom2"].ToString() == endPos || row["Cableto2"].ToString() == endPos) && (Convert.ToBoolean(row["isTestedEnd"]) == true))
                            {
                                erledigtEnd++;
                            }
                        }
                    }

                    foreach (DataRow row in aSCDataSet.vw_bauStatus)
                    {
                        if (row["Positions_ID"].ToString() == startPos)
                        {
                            ergebnis = erledigtStart / gesamtStart * 100;
                            row["cablesTested"] = ergebnis.ToString();
                            row["updateDate"] = DateTime.Today;
                            row["updateUser"] = Properties.Settings.Default.User;
                        }
                        if (row["Positions_ID"].ToString() == endPos)
                        {
                            ergebnis = erledigtEnd / gesamtEnd * 100;
                            row["cablesTested"] = ergebnis.ToString();
                            row["updateDate"] = DateTime.Today;
                            row["updateUser"] = Properties.Settings.Default.User;
                        }
                    }
             
                    // Zurückschreiben in die Datenbank
                    vwbauStatusBindingSource.EndEdit();
                    vw_bauStatusTableAdapter.Update(aSCDataSet.vw_bauStatus);
                    // Datagrid aktualiseren
                    dataGridView1.Update();
                }

            
                // Alternativ nicht über die Tabelle, sondern über das Datagrid

                //for (int b = 0; b < dataGridView1.RowCount; b++ )
                //{
                //    if (dataGridView1.Rows[b].Cells["Positions_ID"].Value.ToString() == startPos)
                //    {
                //        ergebnis = erledigtStart / gesamtStart * 100;
                //        dataGridView1.Rows[b].Cells["cablesInstalled"].Value = ergebnis.ToString();

                //    }
                //    if (dataGridView1.Rows[b].Cells["Positions_ID"].Value.ToString() == endPos)
                //    {
                //        ergebnis = erledigtEnd / gesamtEnd * 100;
                //        dataGridView1.Rows[b].Cells["cablesInstalled"].Value = ergebnis.ToString();

                //    }
                //}
            */
            #endregion prozente


        }

        //
        // Eintrag im Baufortschritt ändert sich
        //
        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            try
            {

                dataGridView1.CurrentRow.Cells["updateDate"].Value = DateTime.Today;
                dataGridView1.CurrentRow.Cells["updateUser"].Value = Properties.Settings.Default.User;
            }
            catch (Exception ex)
            { }
            vwbauStatusBindingSource.EndEdit();
            vw_bauStatusTableAdapter.Update(aSCDataSet.vw_bauStatus);
            
        }

        //
        // Datenbindungen aufheben, wenn Form geschlossen wird
        //
        private void Baufortschritt_FormClosing(object sender, FormClosingEventArgs e)
        {
            dataGridView1.CellValueChanged -= this.dataGridView1_CellValueChanged;
            dataGridView2.CellValueChanged -= this.dataGridView2_CellValueChanged;
            dataGridView1.DataSource = null;
            dataGridView2.DataSource = null;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

            this.vwbauStatusBindingSource.Filter = string.Format("Venue LIKE '{0}' AND Manufacturer LIKE '{1}' AND Model LIKE '{2}' AND ISNULL(JLM,'') LIKE '{3}' AND ISNULL(Positions_ID,'')  LIKE '{4}' ", (textBox1.Text + "*"), (textBox2.Text + "*"), (textBox3.Text + "*"), (textBox4.Text + "*"), (textBox5.Text + "*"));
           
        }

        //
        // Export to Excel
        //
        private void button4_Click(object sender, EventArgs e)
        {

            string pfad = System.Windows.Forms.Application.StartupPath.ToString();
            dataGridView2.SelectAll();
            DataObject dataObj = dataGridView2.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            xlexcel = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                xlWorkBook = xlexcel.Workbooks.Open((pfad + "\\TemplateCablelist.xlsx"));
                xlexcel.Visible = true;
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Cells[1, 8] = dataGridView1.Rows[0].Cells["Venue"].Value.ToString();
                Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[3, 2];
                CR.Select();
                xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
                xlWorkSheet.Cells.Replace("TRUE", "X");
                xlWorkSheet.Cells.Replace("FALSE", "");

            }
            catch (Exception ex)
            {
                MessageBox.Show("Konnte Vorlage für Kabelliste nicht finden");
            }
            dataGridView1.ClearSelection();
        }


    }
}
